﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using prototype123.models;
using prototype123.controllers;
using System.Data;
using System.Collections;

namespace prototype123.data_access
{
    //This class handles all data access between the application and the
    //termitem table in the database.
    class TermItemDAO
    {
        //private SqlConnection db_connection = prototype123.Program.connect.GetConnection();
        private SqlConnection db_connection; // = new SqlConnection("Data Source=sparky;Initial Catalog=cps420_team2_dev;Persist Security Info=True;User ID=cps420team2;Password=team2");

        public TermItemDAO(SqlConnection connection)
        {
            db_connection = connection;
        }

        /*precondition: the system needs TermItem information and has a stuid, termid, and courseid
           description: The system passes in the student's id (stuid), the current term for that
                  student (termid), and the courseid for the course they are taking (courseid). given that
                  info, this method queries the db for the termitem entry matching this info. it receives the
                  info from the query and creates a TermItem object to represent it, and then returns it
          postcondition: the system receives a TermItem object */
        public TermItem Load(int stuid, int termid, string courseid)
        {
            TermItem tm = new TermItem();
            SqlCommand getterm = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and TERM_ID = " + termid + "and COURSE_ID = '" + courseid + "'", db_connection);


            SqlDataReader read = getterm.ExecuteReader();
            read.Read();
            tm.CourseID = courseid;
            tm.Dropped = Convert.ToInt32(read["DROPPED"].ToString());
            tm.ID = Convert.ToInt32(read["ITEM_ID"].ToString());
            tm.TeacherType = read["TEACHER_TYPE"].ToString();
            tm.Textbook = read["TEXTBOOK"].ToString();
            tm.UsingHEICurriculum = Convert.ToInt32(read["USE_HEI_CURR"].ToString());
            //tm.FinalGrade = Convert.ToInt32(read["FINAL_GRADE"]);

            read.Close();
            return tm;
        }

        //redundant code dersch wrote for reasons unknown to mankind. save it just in case.
        //mostly just keep it to mock him.
        /*public void Save(TermItem itm, int stuid)
        {
            SqlCommand save = new SqlCommand("INSERT INTO TERM_ITEM(STU_ID, TERM_ID, COURSE_ID, FINAL_GRADE, DROPPED, " +
                "TEXTBOOK, USE_HEI_CURR, TEACHER_TYPE) VALUES(" + stuid + ", " + itm.ID + ", '" + itm.CourseID +
                "', " + itm.FinalGrade + ", " + itm.Dropped + ", '" + itm.Textbook + "', " + itm.UsingHEICurriculum +
                ", '" + Convert.ToChar(itm.TeacherType) + "')", db_connection);
            save.ExecuteNonQuery();
        }*/

        /*precondition: the educator adds a course AND chooses to use the HEI curriculum
        description: This method copies all default assignments from the PKG_ASMT db and copies them to
              the ASSIGNMENT database and matches it with the termID. It does the same for categories,
              copying them from PKG_ASMT_CAT to ASMT_CAT.
        postconditon: relevant assignments from the PKG_ASMT db and PKG_ASMT_CAT db are now in the
              ASSIGNMENT and ASMT_CAT databases*/
        public void AddCurriculum(int termID)
        {
            SqlCommand AddAssignments = new SqlCommand("INSERT INTO ASSIGNMENT(CAT_ID, TITLE, PTS_POSSIBLE) SELECT" +
                " CAT_ID, TITLE, PTS_POSSIBLE FROM PKG_ASMT WHERE CAT_ID = " + termID, db_connection);

            SqlCommand AddCategories = new SqlCommand("INSERT INTO ASMT_CAT(CAT_NAME, CAT_PERCENT, ITEM_ID) "
                + "SELECT CAT_NAME, CAT_PERCENT, " + termID + " FROM PKG_ASMT_CAT WHERE CAT_ID = " + termID, db_connection);

            AddAssignments.ExecuteNonQuery();
            AddCategories.ExecuteNonQuery();
        }

        /*precondition: an educator registers a student for a course
        description: This registers a student in a class by creating appropriate entries in the db. if the student
              has decided to use the official HEI curriculum, it also calls AddCurriculum to handle the creation
              of default HEI assignments
        postcondition: the student is registered for a course*/
        public void AddCourse(Student std, TermItem termitem)
        {
            SqlCommand add_course = new SqlCommand("INSERT INTO TERM_ITEM(STU_ID, TERM_ID, COURSE_ID," + "DROPPED, " +
                "TEXTBOOK, USE_HEI_CURR, TEACHER_TYPE) Values(" + std.StudentID + "," + termitem.ID + ",'" + termitem.CourseID + "', " +
                 termitem.Dropped + ",'" + termitem.Textbook + "'," + termitem.UsingHEICurriculum + ", '" + Convert.ToChar(termitem.TeacherType) + "')", db_connection);

            add_course.ExecuteNonQuery();

            if (Convert.ToBoolean(termitem.UsingHEICurriculum)) { AddCurriculum(termitem.ID); }
        }

        /*precondition: the educator needs to add a course that was dropped during this term
        description: This method executes a command that clears the DROPPED flag in the
              TERM_ITEM db. This allows the student to be back in the course.
        postcondition: the student is re-enrolled in the course */
        public void ReAddCourse(int stuid, string courseid, int termid)
        {
            SqlCommand re_add_course = new SqlCommand("UPDATE TERM_ITEM SET DROPPED = 0 WHERE " +
                "STU_ID = " + stuid + " and COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);
            re_add_course.ExecuteNonQuery();
        }

        /*precondition: educator attempts to drop a class
        description: This method execuates a command that sets the DROPPED flag in the
              TERM_ITEM db. This drops the student from the class
        postconditon: student is no longer enrolled in the class*/
        public void DropCourse(int stuid, string courseid, int termid)
        {
            SqlCommand drop_course = new SqlCommand("UPDATE TERM_ITEM SET DROPPED = 1 WHERE " +
            "STU_ID = " + stuid + " and COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);


            drop_course.ExecuteNonQuery();
        }

        /*preconditon: system needs to check to see if the student has previously taken and
              passed a course, so that the student does not re-take a passed course
        description: this checks to see if a student is eligible to take a class. If the student has
              taken it and gotten at least a 60% (D-), then he is not eligible to take the class.
              So this checks to see if a record exists where the student 1) took the course and
              2) got a 60 or higher on that course. if there's a match, return true, else false
        postcondition: system receives a bool response on whether it's been passed before*/
        public bool HasPassedCourse(int stuid, string courseid, int termid)
        {
            SqlCommand checkreg = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid + " and " +
                "COURSE_ID = '" + courseid + "' and TERM_ID = " + termid + " and FINAL_GRADE >= 60", db_connection);

            SqlDataReader reader = checkreg.ExecuteReader();
            bool a = reader.Read();
            reader.Close();
            return a;
        }

        public bool IsTakingCourse(int stuid, string courseid, int termid)
        {
            SqlCommand checkreg = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid + " and " +
                "COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);

            SqlDataReader reader = checkreg.ExecuteReader();
            bool a = reader.Read();
            reader.Close();
            return a;
        }


        /*precondition: system needs to know if a student has dropped this course during this term
        description: this uses the stuid, courseid, and termid to locate the DROPPED flag for this
              class and student. it determines if the student has dropped the course and returns
              a true(dropped)/false(hasn't dropped) response to the system
        postconditon: system now knows whether or not the course has been dropped. */
        public bool HasDropped(int stuid, string courseid, int termid)
        {
            SqlCommand dropped = new SqlCommand("SELECT DROPPED FROM TERM_ITEM WHERE STU_ID = " + stuid +
                " and COURSE_ID = '" + courseid + "' and TERM_ID = " + termid, db_connection);
            SqlDataReader read = dropped.ExecuteReader();
            read.Read();

            if (read.HasRows == false)
            {
                read.Close();
                return false;
            }
            int result = Convert.ToInt32(read[0].ToString());
            read.Close();
            return Convert.ToBoolean(result);
        }

        // Dersch experimenting around
        /*
        public int GetCurrentStudentTerm(int stuid)
        {
            SqlCommand id = new SqlCommand("SELECT MAX(TERM_ID) FROM STU_TERM WHERE STU_ID = " + stuid, db_connection);
            SqlDataReader read = id.ExecuteReader();
            read.Read();
            int result = Convert.ToInt32(read[0].ToString());
            read.Close();
            return result;
        }
 
        */
        // Dersch experimenting around again

        public DataTable GetCheckSheetMaybe(int stuid)
        {
            SqlCommand something = new SqlCommand("SELECT * FROM TERM_ITEM WHERE STU_ID = " + stuid, db_connection);
            SqlDataAdapter ad = new SqlDataAdapter(something);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            return dt;
        }


    }
}
